-- @owner: li-xin12345
-- @date: 2023/7/25
-- @testpoint: ustore分区表/二级分区表，非压缩表，修改compresstype=1,compress_chunk_size为2048及compress_prealloc_chunks不同取值，部分合理报错
-- @history: Modified by li-xin12345 2025.2.12：研发代码变更，取消参数控制，适配用例

--step1:建ustore分区表并插入数据;expect:成功
drop table if exists t_alter_rowcompress_0098_01 cascade;
create table t_alter_rowcompress_0098_01
(prod_id number(6),
 cust_id number,
 time_id date,
 channel_id char(1),
 promo_id number(6),
 quantity_sold number(3),
 amount_sold number(10,2)
) with (storage_type = ustore)
partition by range (time_id)
interval('1 day')
( partition p_alter_rowcompress_0098_01_01 values less than ('2023-02-01 00:00:00'),
  partition p_alter_rowcompress_0098_01_02 values less than ('2023-02-02 00:00:00')
);

insert into t_alter_rowcompress_0098_01 values(generate_series(0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-01-10 00:00:00', 'a', 1, 1, 1);
insert into t_alter_rowcompress_0098_01 values(generate_series(
0, 1000), lpad(floor(random() * 1000)::text, 2, '0'), '2023-02-01 00:00:00', 'a', 2, 2, 2);

--step2:分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks为0到3;expect:成功
drop function if exists compress_func_findpath(character varying);
create or replace function compress_func_findpath(character varying)
returns character varying
language plpgsql
as
$body$
declare
relpath character varying;
begin
relpath = (select pg_relation_filepath(relname::regclass) from pg_class where relname = $1);
return relpath;
end;
$body$;
/

alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=0);
checkpoint;
select relname,reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_01_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_01'));

alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=1);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_01_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_01'));

alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=2);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_01_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_01'));

alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=3);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_01_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_01'));

--step3:分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks为4到7;expect:合理报错
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=4);
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=5);
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=6);
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=7);

--step4:分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks取值范围外;expect:合理报错
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=-1);
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=8);

--step5:分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks无效值;expect:合理报错
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks='test');
alter table t_alter_rowcompress_0098_01 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=2.2);

--step6:分区表清理分区表，建ustore二级分区表;expect:成功
drop table if exists t_alter_rowcompress_0098_01 cascade;
drop table if exists t_alter_rowcompress_0098_02 cascade;
create table t_alter_rowcompress_0098_02
(
    month_code varchar2 ( 30 ) not null ,
    dept_code  varchar2 ( 30 ) not null ,
    user_no    varchar2 ( 30 ) not null ,
    sales_amt  int
) with (storage_type = ustore)
partition by list (month_code) subpartition by list (dept_code)
(
  partition p_alter_rowcompress_0098_02_01 values ( '201902' )
  (
    subpartition p_alter_rowcompress_0098_02_01_a values ( '1' ),
    subpartition p_alter_rowcompress_0098_02_01_b values ( '2' )
  ),
  partition p_alter_rowcompress_0098_02_02 values ( '201903' )
  (
    subpartition p_alter_rowcompress_0098_02_02_a values ( '1' ),
    subpartition p_alter_rowcompress_0098_02_02_b values ( '2' )
  )
);

insert into t_alter_rowcompress_0098_02 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0098_02 values('201902', '2', '1', 1);
insert into t_alter_rowcompress_0098_02 values('201902', '1', '1', 1);
insert into t_alter_rowcompress_0098_02 values('201903', '2', '1', 1);
insert into t_alter_rowcompress_0098_02 values('201903', '1', '1', 1);
insert into t_alter_rowcompress_0098_02 values('201903', '2', '1', 1);

--step7:二级分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks为0到3;expect:成功
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=0);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_02_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_02'));

alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=1);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_02_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_02'));

alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=2);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_02_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_02'));

alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=3);
checkpoint;
select relname, reloptions from pg_partition where relname like 'p_alter_rowcompress_0098_02_0%' order by relname;
select is_compress, file_count, logic_size, physic_size, compress_ratio from pg_catalog.compress_ratio_info(compress_func_findpath('t_alter_rowcompress_0098_02'));

--step8:二级分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks为4到7;expect:合理报错
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=4);
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=5);
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=6);
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=7);


--step9:二级分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks取值范围外;expect:合理报错
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=-1);
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=8);

--step10:二级分区表修改压缩参数compresstype=1,compress_chunk_size 2048,compress_prealloc_chunks无效值;expect:合理报错
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks='test');
alter table t_alter_rowcompress_0098_02 set (compresstype=1,compress_chunk_size=2048,compress_prealloc_chunks=2.2);

--step11:清理二级分区表;expect:成功
drop function if exists compress_func_findpath(character varying);
drop table if exists t_alter_rowcompress_0098_02 cascade;